چگونه مانع از ورود داده تکراری در اکسل شوید|ویدئو+نمونه فایل.
سلام به یکی دیگر از آموزش های سافت پلاس خوش آمدید . امروز می خواهیم در زمینه آموزش نرم افزار اکسل با هم مطلبی را مرور کنیم .
حتما تا به حال شده که احتیاج به وارد کردن اطلاعاتی در اکسل داشته باشید اما در عین حال مهم است به نحوی مانع از این شوید که اطلاعات حالت تکراری پیدا کنند . بنابراین باید به نحوی به جلوگیری از ورود داده تکراری در اکسل بپردازیم .
خوب برای این کار چندین و چند راه وجود دارد . اولین راه این است که خود ما کنترل کنیم و به نحوه مانع از مورد داده های تکراری در اکسل شویم .
این هم می تواند راه خوبی باشد اما مطمئنا راههای بهتری هم در این بین وجود دارند .
راههایی که قرار است آن را در این آموزش با هم مورد بررسی قرار دهیم .
من علیرضا شهرآئینی هستم . یک عاشق برنامه نویسی در محیط اکسس و اکسل و در این آموزش می خواهم روش هایی را به شما آموزش دهم که با استفاده از آنها می توانید به نحوی باعث جلوگیری از ورود داده تکراری در اکسل شوید .
پس تا انتهای این مطلب همراه من باشید .
بررسی روش های جلوگیری از ورود داده تکراری در اکسل
بسیاری مواقع پیش می آید که در کار با اکسل به ایجاد لیستهای مورد نظر خود اقدام می نمایید .در حین کار با لیستها یکی از نیازهایی که ممکن است به آن احتیاج پیدا نمایید این است که به جلوگیری از ورود داده تکراری در اکسل توسط کاربر بپردازید .
مسلما سپردن این کار عدم ورود داده تکراری و کنترل آن به کاربر نمی تواند ایده مناسبی برای این کار باشد
اما راه حل اصولی این است که کنترل این امر را به خود اکسل بسپاریم تا به نحو مقتضی این کار را برای ما انجام دهد .
اما چگونه این کار را انجام دهیم ؟
تصویر زیر را در نظر بگیرید
ستون A در این لیست اطلاعات مربوط به کد ملی افراد را در خود ذخیره می نماید حال می خواهیم از اکسل
بخواهیم که در این ستون به هیچ عنوان کد ملی تکراری وارد نشود.
برای این کار چند راه وجود دارد که در ادامه به بررسی آنها خواهیم پرداخت.
ممانعت از ورود داده تکراری در اکسل به کمک خود اکسل
می خواهیم مانع از وارد کردن اطلاعات تکراری توسط کاربر در سلول های اکسل شویم برای این کار مراحل زیر را دنبال نمایید :
محدوده ای از سلول ها را که می خواهید در آنها از ورود داده تکراری توسط اکسل جلوگیری شود را به حالت
انتخاب درآورید برای این منظور ما ردیف سلول های A1: A10 را انتخاب می کنیم
حال به سربرگ Data رفته و گزینه Data validation را انتخاب نمایید .
اکنون در پنجره باز شده در کادر مربوط به Allow گزینه Custom را انتخاب نمایید .
در این مرحله فرمول زیر را در کادر مربوط به Formula وارد نمایید .
countif($A2$:$A$10,A2)<=1=
- اکنون می خواهیم در صورت ورود داده تکراری در این سری از سلول ها پیغام مناسبی از سوی اکسل به کاربر نمایش داده شود برای این منظور وارد سربرگ Error Alert شده تنظیمات مربوطه را مطابق مراحل زیر انجام دهید .
- ابتدا تیک گزینه show error alert after invalid data is entered را بزنید .
- در کادر مربوط به title یک عنوان برای پنجره پیغامی که قرار است به کاربر نمایش داده شود وارد نمایید .
- در کادر مربوط به Error message متن پیغامی را که می خواهید به کاربر نمایش داده شود را وارد کنید .
- در کادر مربوط به Style سه گزینه قرار دارد که بسته به انتخاب شما بعد از وارد نمودن مقدار تکراری اکسل بسته به نوع انتخابی که در این قسمت نموده ا ید عملکرد متفاوتی را به نمایش می گذارد به این ترتیب که :
- اگر می خواهید اکسل به هیچ عنوان اجازه ورود داده تکراری را به کاربر ندهد ا گزینه Stop را در این کادر انتخاب کنید آنگاه در حین اجرای برنامه کادر خطایی شامل پیغام مورد نظر شما با گزینه های موجود در تصویر زیر در اختیار کاربر قرار می گیرد .
همانگونه که مشاهده می کنید کاربر می تواند با انتخاب دکمه Retry دوباره داده مورد نظر را وارد کند یا با انتخاب دکمه Cancel به کل از این کار انصراف دهد
۲ . اگر می خواهید که انتخاب این که اکسل داده تکراری را قبول نماید به عهده کاربر بگذارید گزینه Warning را انتخاب نمایید در این صورت پیغامی مشابه تصویر زیر به کاربر نمایش داده می شود .
در این پنجره کاربر مطلع می شود که داده ای که وارد نموده تکراری است و تصمیم را به عهده کاربر می گذارد و برای ادامه کار از او سوال می شود که اگر گزینه Yes را انتخاب نماید اکسل داده تکراری را از کاربر می پذیرد اما اگر گزینه No را انتخاب نماید داده تکراری را نخواهد پذیرفت .
۳٫ آخرین گزینه نیز تحت عنوان Information می باشد که با انتخاب آن پیغامی مشابه تصویر زیر در اختیار کاربر قرار می گیرد
این پیغام فقط جنبه اطلاع رسانی برای کاربر دارد و به او اطلاع می دهد که اطلاعات وارد شده تکراری است و با قبول اطلاعات هیچ ممانعتی از ورود اطلاعات از سوی اکسل به عمل نخواهد آمد.
این از اولین روشی بود که با هم آن را بررسی کردیم .
اما نظرتان چیست که کمی کار را حرفه ای تر کنیم .
یعنی همین کار را با استفاده از یک روش کاملا حرفه ای همه این کارها را انجام دهیم .
منظورم از این روش حرفه ای استفاده از کد نویسی و برنامه نویسی VBA است .
پس باز هم از شما می خواهم که همچنان همراه من باشید تا از این کدها هم بتوانیم به نحو مطلوبی بهره ببریم .
جلوگیری از ورود داده تکراری با کدهای VBA
خوب برای شروع به سراغ شیت کاری مورد نظر خود رفته و در رویداد مربوط به Change آن شروع به کد نویسی می کنیم .
(Private Sub Worksheet_SelectionChange(ByVal Target As Range
End Sub
در قدم اول باید محدوده مورد نظری را که می خواهیم باعث جلوگیری از ورود داده تکراری در آن شویم را به برنامه خود معرفی کنیم.
برای این کار اول دو متغیر تعریف می کنیم .
متغیر اول قرار است محدوده مورد نظری را که به هیچ عنوان قرار نیست حاوی اطلاعات تکراری باشد را در خود ذخیره می کند .
متغیر دوم هم نماینده هر کدام از سلول های موجود در این محدوده است.
حالا محدوده مورد نظر را در داخل متغیر اول قرار می دهیم .
(Private Sub Worksheet_SelectionChange(ByVal Target As Range
Dim myrange As Range
Dim mycell As Range
("Set myrange = Worksheets("sheet1").Range("b2:d10
End Sub
این کار را با استفاده کد موجود در خط ۷ انجام می دهیم .
حالا برویم به سراغ اینکه تشخیص بدهیم که آیا داده ای که کاربر وارد می کند حالت تکراری دارد یا نه .
پس در ادامه کدهای خود را برای این کار تکمیل می کنیم .
()Private Sub CommandButton1_Click
Dim myrange As Range
Dim mycell As Range
("Set myrange = Worksheets("sheet1").Range("b2:d10
For Each mycell In myrange
If WorksheetFunction.CountIf(myrange, mycell.Value) > 1 Then
"قبلا وارد شده و تکراری است " & MsgBox mycell.Value
End If
Next
End Sub
در این کدها خطوط ۹ و ۱۱ برنامه ما تشخیص می دهد که آیا داده وارد شده در هر سلول در کل محدوده مورد نظر ما بیشتر از ۱ بار وارد شده یا نه ؟
اگر تعداد موارد مربوط به این داده بیشتر از ۱ باشد یعنی اینکه این داده تکراری می باشد .
حالا که این داده را تشخیص دادیم می توانیم هر کاری را که می خواهیم انجام دهیم .
مثلا در کدهای بالا ما یک پیغام را تنظیم کرده ایم که به کاربر نمایش داده شود .
یا نظرتان چیست که مثلا رنگ سلول مورد نظر را به رنگ زرد درآوریم .
()Private Sub CommandButton1_Click
Dim myrange As Range
Dim mycell As Range
("Set myrange = Worksheets("sheet1").Range("b2:d10
For Each mycell In myrange
If WorksheetFunction.CountIf(myrange, mycell.Value) > 1 Then
"قبلا وارد شده و تکراری است " & MsgBox mycell.Value
mycell.Interior.Color = vbYellow
End If
Next
End Sub
این کار را در کد موجود در خط شماره ۱۵ وارد کرده ایم .
در این مطلب سعی کردم که تمامی مطالب مربوط به نحوه ممانعت و جلوگیری از اطلاعات و داده های تکراری در اکسل را برای شما بیان کنم به امید اینکه این آموزش برای شما مفید باشد.
راستی ما برای کار با داده های تکراری مطالب دیگری هم در سایت داریم .
مثلا اگر بخواهید موارد تکراری در یک محدوده را شمارش کنید می توانید از مطلب شناسایی و شمارش داده های تکراری در اکسل به ۲ روش مختلف استفاده کنید .
و یا اگر می خواهید روش دیگری در کار با کدهای وی بی ای را برای یافتن مقادیر تکراری بررسی کنید به شما توصیه می کنم که حتما سری به مطلب یافتن داده های تکراری در اکسل از طریق کدهای VBA بزنید .
در ضمن من در پیج اینستاگرام مربوط به سایت کلی ترفندهای کار در خصوص کار با نرم افزار اکسل گذاشتم.
به عنوان نمونه همین آموزشی را که در اینجا خواندید را می توانید در قالب یک فایل ویدئویی در قسمت ۱۰۱ پرسش و پاسخ در نرم افزار اکسل قسمت-۲۱ و ۲۲ مشاهده کنید .
برای دسترسی به این مطالب و رفتن به پیج اینستاگرام من کافی است روی لینک زیر کلیک کنید .
منتظرتون هستم .
فکر می کنید که این مطلب چه چیز دیگه ای برای تکمیل تر شدن کم داره .
خوشحال می شم نظرتون رو در باره این آموزش برای من در قسمت نظرات درج کنید .
مطالب زیر را حتما مطالعه کنید
Xlookup در اکسل- تابعی با کلی قابلیت های کاربردی
تابع Hlookup در اکسل💥نکات و ترفندهای کار با آن +ویدئو
تابع فیلتر در اکسل 💥به همراه یک مثال کاربردی
تابع سفارشی در اکسل 💥چطور تابع مدنظرتان را در اکسل ایجاد کنید ؟
تابع countif در اکسل💚راهنمای کامل کار با آن
زوم کردن در سلول های اکسل + ۳ روش مختلف
14 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
در مرحله فرمول نویسی خطا میدهد و مساوی اول فرمول را خطا میگیرد.
با سلام
راجع به این مطلب به زودی در قالب ۱۰۱ پرسش وپاسخ در اکسل یک کلیپ آموزشی تهیه و در پیج اینستاگرام سایت قرار می گیرد
به مساوی ربطی نداره
مشکل اینه که به جای علامت ; از علامت , گفته استفاده کن
با سلام دورود فراوان
ضمن خسته نباشید و قدیر و تشکر از زحمات بی شایان یک سوال داشتم در مورد تکراری بودن در اکسل فومول countif($A2$:$A$50,A2)<=۱= خوب جواب مده ولی موقع که با Sheet2.Range("a15") = TextBox1.Value با TextBox تکراری را وارد میکنم هیچ اخطاری نمی دهد فقط موقع تایپ اخطار می دهد اگر در این مورد راهنمایی فرمایید لطف کرده اید چون من برنامه ای دارم که با TextBox وارد می کنم می خواهم اگر تکراری شد آنموقع هم اخطار و از ثبت کردنش جلویی کند با تقدیر و تشکر انوری
سلام در صورت امکان متن اخطار را درج کنید تا راهنمایی شود
سلام.من کد VBA شما را استفاده کردم و ممنونم.میخواستم بعد از انکه پیام خطا به کاربر نشان میدهد مکان نما روی همان سلولی که باعث خطا شده (تکراری هست) قرار بگیرد .باید چه کدی نوشت؟
باز هم تشکر از شما
سلام.ممنون از شما
کدی که نوشتید دوبار پیام خطا را نمایش میدهد و همچنین بعد از فشاردادن دکمه OKتوسط کاربر مکان نما روی همان خانه ای که داده تکراری دارد نمی ایستد .اگر بخواهم این دو مشکل حل شود چکار کنم
ممنون از اموزشتان درصورت امکان یه نمونه فایل ازمانع کد تکراری بفزستید
سلام به زودی نمونه فایل قرار داده خواهد شد
لطفا فایل بفرستید
آموزش اول بسیار عالی با تصویر و ویدئو همراه بود کاش آموزشVBA هم همین طور بود
من هم اطلاعاتی دارم که باید با کپی /پست وارد کنم با اموزش اول قابل اجرا نیست
سلام ویدئوی مربوطه به ابتدای صفحه اضافه شد
عالی بود دمتون گرم
استاد من روش اول رو امتحان کردم وقتی که خودمونیم تایپ می کنیم جواب میده اما وقتی که داده رو کپی پیست می کنیم جواب نمیده؟
درسته؟